{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MetaData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData\n",
    "\n",
    "metadata_obj = MetaData()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Table, Column, types\n",
    "\n",
    "user_table = Table(\n",
    "    'user_account',\n",
    "    metadata_obj,\n",
    "    Column('id', types.Integer, primary_key=True),\n",
    "    Column('name', types.String(30)),\n",
    "    Column('fullname', types.String)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Column('name', String(length=30), table=<user_account>)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.c.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id', 'name', 'fullname']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.c.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PrimaryKeyConstraint(Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False))"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table.primary_key"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 外鍵"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import ForeignKey\n",
    "\n",
    "address_table = Table(\n",
    "    'address',\n",
    "    metadata_obj,\n",
    "    Column('id', types.Integer, primary_key=True),\n",
    "    Column('user_id', ForeignKey('user_account.id'), nullable=False),\n",
    "    Column('email_address', types.String, nullable=False)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:35,140 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:35,141 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n",
      "2024-08-29 08:52:35,141 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,142 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"user_account\")\n",
      "2024-08-29 08:52:35,142 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,143 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n",
      "2024-08-29 08:52:35,144 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,145 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"address\")\n",
      "2024-08-29 08:52:35,145 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,147 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE user_account (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR(30), \n",
      "\tfullname VARCHAR, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2024-08-29 08:52:35,148 INFO sqlalchemy.engine.Engine [no key 0.00091s] ()\n",
      "2024-08-29 08:52:35,155 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE address (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tuser_id INTEGER NOT NULL, \n",
      "\temail_address VARCHAR NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(user_id) REFERENCES user_account (id)\n",
      ")\n",
      "\n",
      "\n",
      "2024-08-29 08:52:35,156 INFO sqlalchemy.engine.Engine [no key 0.00164s] ()\n",
      "2024-08-29 08:52:35,163 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('sqlite+pysqlite:///:memery:', echo=True)\n",
    "\n",
    "metadata_obj.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Declarative Base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import DeclarativeBase \n",
    "\n",
    "class Base(DeclarativeBase):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MetaData()\n"
     ]
    }
   ],
   "source": [
    "print(Base.metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<sqlalchemy.orm.decl_api.registry object at 0x7f9bd755aeb0>\n"
     ]
    }
   ],
   "source": [
    "print(Base.registry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from typing import Optional\n",
    "from sqlalchemy.orm import (\n",
    "    Mapped,\n",
    "    mapped_column,\n",
    "    relationship,\n",
    ")\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'user_account'\n",
    "    id: Mapped[Optional[int]] = mapped_column('id', types.Integer, primary_key=True)\n",
    "    addresses: Mapped[list[\"Address\"]] = relationship(\"Address\", foreign_keys=\"[Address.user_id]\", back_populates=\"user\")\n",
    "\n",
    "    name: Mapped[str] = mapped_column('name', types.String(30))\n",
    "    fullname: Mapped[Optional[str]] = mapped_column('fullname', types.String, nullable=True)\n",
    "\n",
    "\n",
    "class Address(Base):\n",
    "    __tablename__ = 'address' \n",
    "    id: Mapped[Optional[int]] = mapped_column('id', types.Integer, primary_key=True)\n",
    "    user_id = mapped_column('user_id', ForeignKey(\"user_account.id\"), nullable=False)\n",
    "    user: Mapped[\"User\"] = relationship(\"User\", foreign_keys=[user_id], back_populates='address')\n",
    "\n",
    "    email_address: Mapped[str] = mapped_column('email_address', types.String)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "140307309372272\n",
      "140307309372272\n"
     ]
    }
   ],
   "source": [
    "print(id(Base.metadata))\n",
    "print(id(User.metadata))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:35,265 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:35,266 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"user_account\")\n",
      "2024-08-29 08:52:35,267 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,268 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"address\")\n",
      "2024-08-29 08:52:35,269 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,269 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 反射"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_table = Table('user_account', Base.metadata, autoload_with=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('user_account', MetaData(), Column('id', Integer(), table=<user_account>, primary_key=True), Column('name', String(length=30), table=<user_account>, nullable=False), Column('fullname', String(), table=<user_account>), schema=None)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 映射类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "class AddressClass(Base):\n",
    "    __table__ = Table(\n",
    "        'address',\n",
    "        Base.metadata,\n",
    "        autoload_with=engine,\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('address', MetaData(), Column('id', Integer(), table=<address>, primary_key=True), Column('user_id', Integer(), ForeignKey('user_account.id'), table=<address>, nullable=False), Column('email_address', String(), table=<address>, nullable=False), schema=None)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AddressClass.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024-08-29 08:52:35,303 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2024-08-29 08:52:35,303 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2024-08-29 08:52:35,304 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,304 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_temp_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2024-08-29 08:52:35,305 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,305 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"some_table\")\n",
      "2024-08-29 08:52:35,305 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,306 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2024-08-29 08:52:35,306 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)\n",
      "2024-08-29 08:52:35,307 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"some_table\")\n",
      "2024-08-29 08:52:35,307 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,308 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"some_table\")\n",
      "2024-08-29 08:52:35,308 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,308 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2024-08-29 08:52:35,308 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)\n",
      "2024-08-29 08:52:35,310 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"some_table\")\n",
      "2024-08-29 08:52:35,311 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,311 INFO sqlalchemy.engine.Engine PRAGMA temp.index_list(\"some_table\")\n",
      "2024-08-29 08:52:35,311 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,312 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"some_table\")\n",
      "2024-08-29 08:52:35,312 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,313 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"some_table\")\n",
      "2024-08-29 08:52:35,313 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,314 INFO sqlalchemy.engine.Engine PRAGMA temp.index_list(\"some_table\")\n",
      "2024-08-29 08:52:35,314 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,314 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"some_table\")\n",
      "2024-08-29 08:52:35,314 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2024-08-29 08:52:35,315 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2024-08-29 08:52:35,315 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)\n",
      "2024-08-29 08:52:35,317 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.reflect(engine)\n",
    "\n",
    "class MyAddress(Base):\n",
    "    __table__ = Base.metadata.tables['address']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('address', MetaData(), Column('id', Integer(), table=<address>, primary_key=True), Column('user_id', Integer(), ForeignKey('user_account.id'), table=<address>, nullable=False), Column('email_address', String(), table=<address>, nullable=False), schema=None)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MyAddress.__table__"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
